-- **************************************************************************
--    Project Name:   网点基础资料概要表
--    Job Name:       jms_dim.dim_network_whole_massage
--    Description :   将网点相关常用数据合并在一张维表中,使用方便
--    Author :        申家明
--    date：          2022/04/25
--    date：          2022/08/05
-- **************************************************************************
--    Relation ：
--    jms_dim.dim_network_whole_massage << [
--        jms_ods.yl_lmdm_sys_network,
--        jms_ods.sys_manage_region,
--        jms_ods.yl_lmdm_sys_network_delivery_r,
--        jms_ods.yl_lmdm_sys_network_electronic,
--        jms_ods.yl_lmdm_sys_network_expand,
--        jms_ods.yl_lmdm_sys_network_distributi
--    ]
-- **************************************************************************
with t_network_base as (
    select *
         , is_virtual_agent
         , split(path, '/')[1] as path_1
         , split(path, '/')[2] as path_2
         , split(path, '/')[3] as path_3
         , split(path, '/')[4] as path_4
         , split(path, '/')[5] as path_5
         , split(path, '/')[6] as path_6
         , case
               when type_id = 22 then 1
               when type_id = 334 then 2
               when type_id = 335 and (is_first_franchisee = 1 or is_second_franchisee = 1) then 3
               when type_id = 335 and is_distribution_center = 1 then 4
               when type_id = 336 and is_entrepot = 1 then 5
               when type_id = 336 and is_entrepot = 2 then 6
        end                    as network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
    from jms_ods.yl_lmdm_sys_network --网点资料 --网点资料
    where dt = '{{ execution_date | cst_ds }}'
      and is_delete = 1
      and code not like '%test%'
      and name not like '%test%'
      and code not like '%Test%'
      and code not like '%测试%'
      and name not like '%测试%'
      and code not like '%suger%'
      and name not like '%验证%'
      and code not like '%text%'
      and name not like '%text%'
      and if(type_id = 334 and length(code) = 6 and substr(code,1,4) = 'XNDL',1,0) = 0
      and if(type_id = 335 and is_distribution_center = 1 and length(code) = 6 and substr(code,1,2) = 'XN',1,0) = 0
      and if(type_id = 335 and (is_first_franchisee = 1 or is_second_franchisee = 1) and length(code) = 9 and substr(code,1,4) = 'XNJM',1,0) = 0
      and if(type_id = 336 and is_entrepot = 2 and length(code) = 7 and substr(code,1,2) = 'XN',1,0) = 0
      --    代理区层级：XNDL+2位递增码，例如：XNDL01
      --    转运中心层级：XN+4位递增码，例如：XN0001
      --    加盟商层级：XNJM+5位递增码，例如：XNJM00001
      --    网点层级：XN+5位递增码，例如：XN00001
)
,t_network_virtual as (
    select id   as virt_id   --虚拟代理区id
         , code as virt_code --虚拟代理区code
         , name as virt_name --虚拟代理区name
    from jms_ods.yl_lmdm_sys_network --网点资料 --网点资料
    where dt = '{{ execution_date | cst_ds }}'
      and is_virtual_agent = 1
      and code not like '%test%'
      and name not like '%test%'
      and code not like '%Test%'
      and code not like '%测试%'
      and name not like '%测试%'
      and code not like '%suger%'
      and name not like '%验证%'
      and code not like '%text%'
      and name not like '%text%'
)
,t_network as (
    select a.*
         , b.virt_id                            --虚拟代理区id
         , b.virt_code                          --虚拟代理区code
         , b.virt_name                          --虚拟代理区name
         , case
               when nvl(path_2, 'cs') = virt_id and is_virtual_agent <> 1 then path_3
               else path_2 end as agent_id      --代理区id
         , case
               when nvl(path_2, 'cs') = virt_id and is_virtual_agent <> 1 then path_4
               else path_3 end as franchisee_id --中心id或者加盟商id
    from t_network_base a
             left join t_network_virtual b
                       on nvl(a.path_2, 'cs') = b.virt_id
)
,t_manage as (
    select code      as manage_code      --管理大区名称
         , name      as manage_name      --管理大区名称
         , is_delete as manage_is_delete --管理大区是否删除
         , proxy_network_id              --代理区ID列表
    from jms_ods.sys_manage_region --管理大区表
    where dt = '{{ execution_date | cst_ds }}'
)
,t_delivery as (
    select network_code
         , CONCAT_WS(',', COLLECT_SET(cast(is_dispatch as string)))     as delivery_is_dispatch  --派件范围标识
         , CONCAT_WS(',', COLLECT_SET(cast(is_receiving as string)))    as delivery_is_receiving --收件范围标识
         , CONCAT_WS(',', COLLECT_SET(cast(is_delete as string)))       as delivery_is_delete    --是否删除:1未删除,2已删除
         , max(delivery_range)                                          as delivery_range        --派件范围
         , max(receiving_range)                                         as receiving_range       --收件范围
         , CONCAT_WS(',', COLLECT_SET(cast(non_dr_range as string)))    as non_dr_range          --非收派范围
         , CONCAT_WS(',', COLLECT_SET(cast(place_name as string)))      as place_name            --场地名称
         , CONCAT_WS(',', COLLECT_SET(cast(sign_addr_range as string))) as sign_addr_range       --签卡地址范围
    from jms_ods.yl_lmdm_sys_network_delivery_r --基础网点信息合并
    where dt = '{{ execution_date | cst_ds }}'
      and is_enable = 1
    group by network_code
)
,t_electronic as (
    select max(if(delivery_type = 1, 1, 0)) as receiving_type         --收件标识,1代表收件
         , max(if(delivery_type = 2, 1, 0)) as dispatch_type          --派件标识,1代表派件
         , max(delivery_shape)              as delivery_shape         --收派件围栏-形状1多边形2圆形
         , max(g_id)                        as g_id                   --高德地图主键
         , max(g_name)                      as g_name                 --高德地图围栏名称
         , max(g_center)                    as g_center               --圆形围栏中心点
         , max(g_points)                    as g_points               --电子围栏坐标
         , max(g_radius)                    as g_radius               --圆形围栏半径
         , max(g_valid_time)                as g_valid_time           --高德地图-过期日期
         , max(g_create_time)               as g_create_time          --高德地图-围栏创建时间
         , max(expiry_date)                 as electronic_expiry_date --失效日期
         , network_id
    from jms_ods.yl_lmdm_sys_network_electronic --网点电子围栏
    where dt = '{{ execution_date | cst_ds }}'
    group by network_id
)
,t_expand as (
    select code
         , max(in_distribution_id)    as in_distribution_id    --可交货转运中心id
         , max(in_distribution_code)  as in_distribution_code  --可交货转运中心编码
         , max(in_distribution_name)  as in_distribution_name  --可交货转运中心名称
         , max(out_distribution_id)   as out_distribution_id   --可派货转运中心id
         , max(out_distribution_code) as out_distribution_code --可派货转运中心编码
         , max(out_distribution_name) as out_distribution_name --可派货转运中心名称
         , max(entrepot_id)           as entrepot_id           --集散网点id
         , max(entrepot_code)         as entrepot_code         --集散网点编码
         , max(entrepot_name)         as entrepot_name         --集散网点名称
    from jms_ods.yl_lmdm_sys_network_expand --基础网点信息合并
    where dt = '{{ execution_date | cst_ds }}'
    group by code
)
,t_distributi as (
    select network_id
         , CONCAT_WS(',', COLLECT_SET(cast(ph_center_id as string)))     as ph_center_id     --可派货中心id
         , CONCAT_WS(',', COLLECT_SET(cast(ph_center_code as string)))   as ph_center_code   --可派货中心code
         , CONCAT_WS(',', COLLECT_SET(cast(ph_center_name as string)))   as ph_center_name   --可派货中心name
         , CONCAT_WS(',', COLLECT_SET(cast(jh_center_id as string)))     as jh_center_id     --可交货中心id
         , CONCAT_WS(',', COLLECT_SET(cast(jh_center_code as string)))   as jh_center_code   --可交货中心code
         , CONCAT_WS(',', COLLECT_SET(cast(jh_center_name as string)))   as jh_center_name   --可交货中心name
         , CONCAT_WS(',', COLLECT_SET(cast(jh_entrepot_id as string)))   as jh_entrepot_id   --可交货集散id
         , CONCAT_WS(',', COLLECT_SET(cast(jh_entrepot_code as string))) as jh_entrepot_code --可交货集散code
         , CONCAT_WS(',', COLLECT_SET(cast(jh_entrepot_name as string))) as jh_entrepot_name --可交货集散name
         , CONCAT_WS(',', COLLECT_SET(cast(ph_entrepot_id as string)))   as ph_entrepot_id   --可派货集散id
         , CONCAT_WS(',', COLLECT_SET(cast(ph_entrepot_code as string))) as ph_entrepot_code --可派货集散code
         , CONCAT_WS(',', COLLECT_SET(cast(ph_entrepot_name as string))) as ph_entrepot_name --可派货集散name
    from (
             select network_id        --网点ID
                  , id                --网点ID
                  , code              --交派货分拨编码
                  , name              --交派货分拨名称
                  , distribution_type --1、可派货分拨 2、可交货分拨 3、可交货集散 4、可派货集散
                  , case when distribution_type = 1 then id end   as ph_center_id
                  , case when distribution_type = 1 then code end as ph_center_code
                  , case when distribution_type = 1 then name end as ph_center_name
                  , case when distribution_type = 2 then id end   as jh_center_id
                  , case when distribution_type = 2 then code end as jh_center_code
                  , case when distribution_type = 2 then name end as jh_center_name
                  , case when distribution_type = 3 then id end   as jh_entrepot_id
                  , case when distribution_type = 3 then code end as jh_entrepot_code
                  , case when distribution_type = 3 then name end as jh_entrepot_name
                  , case when distribution_type = 4 then id end   as ph_entrepot_id
                  , case when distribution_type = 4 then code end as ph_entrepot_code
                  , case when distribution_type = 4 then name end as ph_entrepot_name
             from jms_ods.yl_lmdm_sys_network_distributi --1、可派货分拨 2、可交货分拨 3、可交货集散 4、可派货集散
             where dt = '{{ execution_date | cst_ds }}'
--                     and network_id = '1579'
         ) a
    group by network_id
)
-- 网点所属片区数据
,t_zone as (
    select * from (
        select
             t1.code
            ,t1.zone_code
            ,t1.zone_name
            ,t1.zone_id
            ,t2.regional_contact_code
            ,t2.regional_contact_name
            ,t2.area_type
            ,t3.staff_name
            ,t3.staff_code
            ,row_number() over(partition by t1.code order by t1.update_time desc) as row_id
        from jms_ods.sys_network_area t1
        left join (
            select *
            from jms_ods.sys_agency_area
            where dt = '{{ execution_date | cst_ds }}'
            and is_delete = 1
            and is_enable = 1
        ) t2
        on t1.zone_code = t2.code
        left join (
            select
                agency_area_id
                ,concat_ws(',',collect_set(staff_name)) as staff_name
                ,concat_ws(',',collect_set(staff_code)) as staff_code
            from jms_ods.sys_agency_area_manager
            where dt = '{{ execution_date | cst_ds }}'
            group by agency_area_id
        ) t3
        on t1.zone_id = t3.agency_area_id
        where t1.dt = '{{ execution_date | cst_ds }}'
        and t1.is_enable = 1
    ) t
    where row_id = 1
),
tmp_area as(
select
max(a.code) as code,
max(a.name) as name,
b.next_network_code as next_network_code,
max(a.head_staff_code) as area_staff_code,
max(a.head_staff_name) as area_staff_name,
max(a.area_id) as  sys_area_id,
max(a.area_code) as parent_code,
max(c.name) as parent_name,
max(a.id) as leaf_area_id
from jms_dim.dim_sys_area_info a
left join jms_dim.dim_sys_area_info_jt b
on a.code = b.area_info_code
left join jms_dim.dim_sys_area_info c
on a.area_id = c.id
group by b.next_network_code
)

insert overwrite table jms_dim.dim_network_whole_massage
select net.id                                                     --主键ID
     , net.name                                                   --名称
     , net.code                                                   --编号
     , net.signature_code                                         --特征码
     , net.cn_name                                                --中文名称
     , net.en_name                                                --英文名称
     , net.country_id                                             --所属国家
     , net.country_code                                           --所属国家编码
     , net.country_desc                                           --所属国家名称
     , net.country_three_code                                     --所属国家三字码
     , net.regional_id                                            --大区ID
     , net.regional_desc                                          --大区名称
     , net.area_info_id                                           --片区ID
     , net.area_info_desc                                         --片区名称
     , net.provider_id                                            --省份ID
     , net.provider_desc                                          --省份
     , net.city_id                                                --城市ID
     , net.city_desc                                              --城市
     , net.area_id                                                --区/县ID
     , net.area_desc                                              --区/县
     , net.principal                                              --负责人
     , net.mobile                                                 --手机号码
     , net.telephone                                              --座机号
     , net.parent_network_id                                      --所属网点ID
     , net.parent_network_code                                    --所属网点编码
     , net.parent_network_name                                    --所属网点名称
     , net.financial_center_id                                    --财务中心ID
     , net.financial_center_code                                  --财务中心CODE
     , net.financial_center_desc                                  --财务中心名称
     , net.function_type_id                                       --功能类型ID
     , net.function_type_desc                                     --功能类型描述
     , net.network_type                                           --网点类型
     , net.institutional_level_id                                 --机构级别
     , net.institutional_level_desc                               --机构级别描述
     , net.max_collection_money                                   --代收货款上限
     , net.settlement_destination_id                              --结算目的地ID
     , net.settlement_destination_desc                            --结算目的地描述
     , net.is_collection_identifier                               --代收货款标识:1是2否
     , net.business_model_id                                      --经营方式
     , net.business_model_desc                                    --经营方式描述
     , net.local_currency_id                                      --本币币别
     , net.local_currency_code                                    --本币币别编码
     , net.local_currency_desc                                    --本币币别描述
     , net.is_financial_center                                    --财务中心标识:1是2否
     , net.is_waybill_identification                              --运单发放标识:1是2否
     , net.is_to_payment                                          --到付款标识:1是2否
     , net.is_visibility                                          --是否对外显示1是2否
     , net.is_enable                                              --是否启用:1启用2不启用
     , net.is_leaf                                                --是否叶子节点:1是2否
     , net.is_delete                                              --是否删除:1未删除2已删除
     , net.address                                                --详细地址
     , net.business_principal                                     --业务负责人
     , net.business_telephone                                     --业务电话
     , net.customer_service_principal                             --客服负责人
     , net.customer_service_telephone                             --客服电话
     , net.longitude                                              --经度
     , net.latitude                                               --纬度
     , net.create_by                                              --创建人ID
     , net.update_by                                              --最后更新人ID
     , net.create_by_name                                         --创建人名称
     , net.update_by_name                                         --最后修改人名称
     , net.create_time                                            --创建时间
     , net.update_time                                            --更新时间
     , net.version                                                --版本号
     , net.sort                                                   --排序
     , net.remark                                                 --标记
     , net.path                                                   --树路径
     , net.is_receiving                                           --是否有收货电子围栏:1是2否
     , net.is_dispatch                                            --是否有派货电子围栏:1是2否
     , net.zip_code                                               --邮编
     , net.start_business_time                                    --开始营业时间
     , net.end_business_time                                      --结束营业时间
     , net.last_update_time_sync                                  --同步时间戳-
     , net.coordinate                                             --网点经纬度
     , net.is_first_network_settle                                --是否一级网点结算标识1:是2:否
     , net.simple_name                                            --简称
     , net.is_first_franchisee                                    --是否一级加盟商1是2否
     , net.is_second_franchisee                                   --是否二级加盟商1是2否
     , net.township_id                                            --乡镇ID
     , net.to_payment_limit                                       --到付款限额
     , net.opening_type                                           --开通类型:1-新开通2-合并开通3-转让开通，4-拆分开通
     , net.opening_time                                           --开通时间
     , net.is_retrogression                                       --是否退转网点1是2否 默认（2-否）
     , net.is_suspend                                             --是否暂停(1是2否)默认2-否
     , net.leaving_time                                           --暂停时间
     , net.company                                                --所属公司
     , net.busi_license_url                                       --营业执照
     , t_network_agnet.id            as agent_id                  --代理区id
     , t_network_agnet.code          as agent_code                --代理区编码
     , t_network_agnet.name          as agent_name                --代理区名称,
     , t_network_fran.id             as fran_id                   --加盟商id
     , t_network_fran.code           as fran_code                 --加盟商编码
     , t_network_fran.name           as fran_name                 --加盟商名称
     , t_network_center.id           as center_id                 --中心id
     , t_network_center.code         as center_code               --中心编码
     , t_network_center.name         as center_name               --中心名称
     , t_manage.manage_code                                       --管理大区名称
     , t_manage.manage_name                                       --管理大区名称
     , t_manage.manage_is_delete                                  --管理大区是否删除
     , t_delivery.delivery_is_dispatch                            --派件范围标识
     , t_delivery.delivery_is_receiving                           --收件范围标识
     , t_delivery.delivery_is_delete                              --是否删除:1未删除,2已删除
     , t_delivery.delivery_range                                  --派件范围
     , t_delivery.receiving_range                                 --收件范围
     , t_delivery.non_dr_range                                    --非收派范围
     , t_delivery.place_name                                      --场地名称
     , t_delivery.sign_addr_range                                 --签卡地址范围
     , t_electronic.receiving_type                                --收件标识,1代表收件
     , t_electronic.dispatch_type                                 --派件标识,1代表派件
     , t_electronic.delivery_shape                                --收派件围栏-形状1多边形2圆形
     , t_electronic.g_id                                          --高德地图主键
     , t_electronic.g_name                                        --高德地图围栏名称
     , t_electronic.g_center                                      --圆形围栏中心点
     , t_electronic.g_points                                      --电子围栏坐标
     , t_electronic.g_radius                                      --圆形围栏半径
     , t_electronic.g_valid_time                                  --高德地图-过期日期
     , t_electronic.g_create_time                                 --高德地图-围栏创建时间
     , t_electronic.electronic_expiry_date                        --失效日期
     , t_expand.in_distribution_id                                --可交货转运中心id
     , t_expand.in_distribution_code                              --可交货转运中心编码
     , t_expand.in_distribution_name                              --可交货转运中心名称
     , t_expand.out_distribution_id                               --可派货转运中心id
     , t_expand.out_distribution_code                             --可派货转运中心编码
     , t_expand.out_distribution_name                             --可派货转运中心名称
     , t_expand.entrepot_id                                       --集散网点id
     , t_expand.entrepot_code                                     --集散网点编码
     , t_expand.entrepot_name                                     --集散网点名称
     , net.path_1                                                 --path解析,第1个id
     , net.path_2                                                 --path解析,第2个id
     , net.path_3                                                 --path解析,第3个id
     , net.path_4                                                 --path解析,第4个id
     , net.path_5                                                 --path解析,第5个id
     , net.path_6                                                 --path解析,第6个id
     , t_distributi.jh_center_id     as in_distribution_id_new    --可交货转运中心id
     , t_distributi.jh_center_code   as in_distribution_code_new  --可交货转运中心编码
     , t_distributi.jh_center_name   as in_distribution_name_new  --可交货转运中心名称
     , t_distributi.ph_center_id     as out_distribution_id_new   --可派货转运中心id
     , t_distributi.ph_center_code   as out_distribution_code_new --可派货转运中心编码
     , t_distributi.ph_center_name   as out_distribution_name_new --可派货转运中心名称
     , t_distributi.jh_entrepot_id   as in_entrepot_id_new        --可交货集散id
     , t_distributi.jh_entrepot_code as in_entrepot_code_new      --可交货集散code
     , t_distributi.jh_entrepot_name as in_entrepot_name_new      --可交货集散name
     , t_distributi.ph_entrepot_id   as out_entrepot_id_new       --可派货集散id
     , t_distributi.ph_entrepot_code as out_entrepot_code_new     --可派货集散code
     , t_distributi.ph_entrepot_name as out_entrepot_name_new     --可派货集散name
     , net.is_virtual_agent                                       --是否是虚拟代理区,1是,非1不是
     , net.virt_id                                                --虚拟代理区id
     , net.virt_code                                              --虚拟代理区code
     , net.virt_name                                              --虚拟代理区name
     , net.type_id                                                    --网点类型判断,22总部,334代理区,335中心或者加盟商,336集散或者网点
     , net.type_desc                                                  --网点类型描述
     , net.is_distribution_center                                     --是不是分拨中心,1是,其他不是
     , net.subordinate_agent                                          --所属代理区
     , net.is_entrepot                                                --是否是集散,1是,其他不是
     , ''                            as remark2                   --备用
     , ''                            as remark3                   --备用
     , t_zone.zone_code                                               --市场区域维护CODE
     , t_zone.zone_name                                               --市场区域维护名称
     , t_zone.zone_id                                                 --市场区域维护id
     , t_zone.regional_contact_code as zone_regional_contact_code     --市场区域维护联系人编码
     , t_zone.regional_contact_name as zone_regional_contact_name     --市场区域维护联系人名称
     , t_zone.area_type  as zone_area_type                            --市场区域维护管辖区域类型 1城市 2区县
     , t_zone.staff_name as zone_staff_name                           --市场区域维护负责人名称
     , t_zone.staff_code as zone_staff_code                           --市场区域维护负责人CODE
     ,t_area.code as area_code                   --网管片区编码
     ,t_area.name as area_name                   --网管片区名称
     ,t_area.area_staff_code                     --网管片区负责人code
     ,t_area.area_staff_name                     --网管片区负责人name
     ,t_area.sys_area_id                         --网管片区id
     ,t_area.parent_code as parent_area_code     --网管父片区域code
     ,t_area.parent_name as parent_area_name     --网管父片区域name
     ,t_area.leaf_area_id as  leaf_area_id       --网管最细粒度片区id
from t_network as net
left join t_network as t_network_agnet
          on net.agent_id = t_network_agnet.id and t_network_agnet.network_type = 2
left join t_network as t_network_fran
          on net.franchisee_id = t_network_fran.id and t_network_fran.network_type = 3
left join t_network as t_network_center
          on net.franchisee_id = t_network_center.id and t_network_center.network_type = 4
left join t_manage as t_manage
          on net.agent_id = t_manage.proxy_network_id
left join t_delivery as t_delivery
          on net.code = t_delivery.network_code
left join t_electronic as t_electronic
          on t_electronic.network_id = net.id
left join t_expand as t_expand
          on t_expand.code = net.code
left join t_distributi as t_distributi
          on t_distributi.network_id = net.id
left join t_zone as t_zone
          on net.code = t_zone.code
left join tmp_area as t_area
          on net.code = t_area.next_network_code
distribute by 1
;